﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using TrainingWorkshop.Model;


namespace TrainingWorkshop.DAL
{
    public class DatabaseOP
    {
        private SqlConnection sqlconn;

        public DatabaseOP(string connstring)//constructor
        {

            sqlconn = new SqlConnection(connstring);

        }

        //public bool ConnectDatabase()
        //{

        //    bool status;
        //    try
        //    {
        //        sqlconn.Open();
        //        status = true;

        //    }
        //    catch//(Exception err) 
        //    {
        //        status = false;

        //    }

        //    return status;
        //}

        //public bool CloseDatabase()
        //{
        //    bool status;
        //    try
        //    {
        //        sqlconn.Close();
        //        status = true;

        //    }
        //    catch//(Exception err )
        //    {
        //        status = false;

        //    }

        //    return status;
        //}

        public DataSet SelectOP(bool all, int TeacherId = 0)//all is true for selectall,otherwise select exact row for gridview
        {
            DataSet ds = new DataSet();


            string strSE = null;
            if (all)
            {
                strSE = "select TeacherId,TeacherName,TeacherGender,DepartmentName from Teacher a ,Department b where a.DepartmentId=b.DepartmentId";
            }

            else
            {
                strSE = "select TeacherId,TeacherName,TeacherGender,DepartmentName from Teacher a ,Department b where a.DepartmentId=b.DepartmentId and TeacherId=@TeacherID";

            }

            SqlCommand cmd = new SqlCommand(strSE, sqlconn);
            SqlParameter[] par = new SqlParameter[]
            {
                new SqlParameter("@TeacherID",TeacherId)
            };
            cmd.Parameters.AddRange(par);

            try
            {
                sqlconn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(strSE, sqlconn);
                adapter.Fill(ds, "Result");
                sqlconn.Close();
            }

            catch (Exception e)
            {
                HttpContext.Current.Response.Write("<script>alert('Select Error')</script>");
                sqlconn.Close();
            }

            return ds;
        }

        public List<Department> SelectFromDepartmnet(bool all, int DepartId = 0)
        {
            DataSet ds = new DataSet();
            List<Department> result = new List<Department>();
            string str = null;
            if (all)
            {
                str = "select * from Department ";
            }
            else
            {
                str = "select * from Department where DepartmentId=@DepartId";
            }

            SqlParameter par = new SqlParameter("@DepartId", DepartId);
            SqlCommand cmd = new SqlCommand(str, sqlconn);
            cmd.Parameters.Add(par);

            try
            {
                sqlconn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Result");
                sqlconn.Close();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Department a = new Department(Convert.ToInt32(row[0]), row[1].ToString().Trim(), row[2].ToString().Trim(), row[3].ToString().Trim(), row[4].ToString().Trim());
                    result.Add(a);
                }

            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write("<script>alert('Department Table Error')</script>");
                sqlconn.Close();
            }

            return result;

        } 

        public bool InsertDepartmentTable(Department dep)
        {

            try
            {
                sqlconn.Open();

                string strDe = "insert into Department (DepartmentName,Manager,Phone,Remark) values (@DepartmentName,@Manager,@Phone,@Remark)";//;select @DepartmentId= SCOPE_IDENTITY() ";

                SqlParameter[] parasDE = new SqlParameter[]

           {

                new SqlParameter("@DepartmentName",dep.DepartmentName),

                new SqlParameter("@Manager",dep.Manager),

                new SqlParameter("@Phone",dep.Phone),

                new SqlParameter("@Remark", dep.Remark),

                //new SqlParameter("@DepartmentId", SqlDbType.Int)

           };

                //parasDE[4].Direction = ParameterDirection.Output;

                SqlCommand cmd = new SqlCommand(strDe, sqlconn);

                cmd.Parameters.AddRange(parasDE);

                cmd.ExecuteNonQuery();

                // int id = Convert.ToInt32(parasDE[4].Value);//插入后取得主键自增id  

                // string strTe = "insert into Teacher (TeacherName,TeacherGender,DepartmentId) values(@TeacherName,@TeacherGender,@DepartmentId)";

                // SqlParameter[] parasTE = new SqlParameter[]

                //{
                // new SqlParameter("@TeacherName",TeacherName),

                // new SqlParameter("@TeacherGender",TeacherGender),

                // new SqlParameter("@DepartmentId",id)

                //};

                // SqlCommand cmd2 = new SqlCommand(strTe, sqlconn);

                // cmd2.Parameters.AddRange(parasTE);

                // cmd2.ExecuteNonQuery();
               
                sqlconn.Close();
                return true;

            }

            catch (Exception e)
            {
                HttpContext.Current.Response.Write("<script>alert('Insert Error')</script>");
                
                sqlconn.Close();
                return false;
               
            }



        }

        public List<Teacher> SelectFromTeacher(bool all, int TeacherId = 0)
        {
            DataSet ds = new DataSet();
            List<Teacher> result = new List<Teacher>();
            string str = null;
       
            if (all)
            {
                str = "select * from Teacher ";
        
            }
            else
            {
                str = "select * from Teacher where TeacherId=@Id";
               
            }

            SqlParameter par = new SqlParameter("@Id", TeacherId);
            SqlCommand cmd = new SqlCommand(str, sqlconn);
            cmd.Parameters.Add(par);

            try
            {
                sqlconn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Result");
                sqlconn.Close();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Teacher a = new Teacher //.........................在用初始化器在做一次
                    {
                        TearcherId = Convert.ToInt32(row[0]),
                        TeacherName = row[1].ToString().Trim(),
                        TeacherGender = row[2].ToString().Trim(),
                        DepartmentId = Convert.ToInt32(row[3])
                    };

                    //Teacher a = new Teacher(Convert.ToInt32(row[0]), row[1].ToString(), row[2].ToString(), Convert.ToInt32(row[3]));

                    result.Add(a);
                }
               
            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write("<script>alert('Teacher Table Error')</script>");
                sqlconn.Close();
            }

            return result;

        }

        public bool InsertTeacherTable(Teacher Variables)
        {

            try
            {
                sqlconn.Open();
                string str = "insert into Teacher (TeacherName,TeacherGender,DepartmentId) values (@Name,@Gender,@Id)";
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@Name",Variables.TeacherName),
                    new SqlParameter("@Gender",Variables.TeacherGender),
                    new SqlParameter("@Id",Variables.DepartmentId)

                };

                SqlCommand cmd = new SqlCommand(str, sqlconn);

                cmd.Parameters.AddRange(para);

                cmd.ExecuteNonQuery();
                sqlconn.Close();
                return true;
            }

            catch (Exception)
            {

                HttpContext.Current.Response.Write("<script>alert('Insert Error')</script>");
                sqlconn.Close();
                return false;
            }
        }

        public bool UpdateTeacherTable(Teacher Variables)
        {
            try
            {
                sqlconn.Open();
                string str = "update Teacher set TeacherName=@Name,TeacherGender=@Gender,DepartmentId=@DId where TeacherId=@Id";
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@Name",Variables.TeacherName),
                    new SqlParameter("@Gender",Variables.TeacherGender),
                    new SqlParameter("@DId",Variables.DepartmentId),
                    new SqlParameter("@Id",Variables.TearcherId)

                };

                SqlCommand cmd = new SqlCommand(str, sqlconn);

                cmd.Parameters.AddRange(para);

                cmd.ExecuteNonQuery();
                sqlconn.Close();
                return true;
            }

            catch (Exception)
            {

                HttpContext.Current.Response.Write("<script>alert('Insert Error')</script>");
                sqlconn.Close();
                return false;
            }
        }

        public bool DeleteTeacherTable(Teacher Variables)
        {
            try
            {
                sqlconn.Open();
                string str = "Delete from Teacher where TeacherId=@Id";
                SqlParameter[] para = new SqlParameter[]
                {
                    
                    new SqlParameter("@Id",Variables.TearcherId)

                };

                SqlCommand cmd = new SqlCommand(str, sqlconn);

                cmd.Parameters.AddRange(para);

                cmd.ExecuteNonQuery();
                sqlconn.Close();
                return true;
            }

            catch (Exception)
            {

                HttpContext.Current.Response.Write("<script>alert('Delete Error')</script>");
                sqlconn.Close();
                return false;
            }
 
        }

        

    }


}
